<?php
	require_once './Tool/DB/SqlDBManager.class.php';
	require_once './Tool/Common/PrepareInput.php';
	include './Reports/conn.php';
	
	$startDate=$endDate=$centreId=$ActivityId="";  
	
	if(!empty($_POST['sortBy'])){
		$sort="client.".$_POST['sortBy'];
		$_SESSION["sortBy"] = $sort;
	}else{
		$sort="client.NRIC";
	}
	$order='asc';

    if(isset($_REQUEST['startDate'])){ 
        $startDate=prepareInput($_REQUEST['startDate']); 
        $startDate = date('Y-m-d', strtotime($_REQUEST['startDate']));
    }
    if(isset($_REQUEST['endDate'])) { 
        $endDate=prepareInput($_REQUEST['endDate']);
        $endDate = date('Y-m-d', strtotime($_REQUEST['endDate'])); 
    }
    if(isset($_REQUEST['centreId'])){ 
        $centreId=prepareInput($_REQUEST['centreId']); 
    }
    if(isset($_REQUEST['activity'])){ 
        $ActivityId=prepareInput($_REQUEST['activity']); 
    }

    //Validate input
    if(!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$startDate)){
        echo "<font color=red>Invalid Start Date Format.</font><br/>";
        exit();
    }
    if(!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$endDate)){
        echo "<font color=red>Invalid End Date Format.</font><br/>";
        exit();
    }
    if(!preg_match("/^[0-9]*$/",$centreId)){
        echo "<font color=red>Invalid Centre ID.</font><br/>";
        exit();
    }
    if(!preg_match("/^[0-9]*$/",$ActivityId)){
        echo "<font color=red>Invalid Activity ID.</font><br/>";
        exit();
    }

	if (!empty($_SESSION['userType'])) {
		$userType = $_SESSION['userType'];
	}
	if ($userType == 'System Admin') {
		$centreId = intval(isset($_POST['submit']) ? $_POST['ddlCentre'] : '' );
	} else {
		include 'conn.php';
		$qry = "Select CentreId from Centre where CentreName='" . $_SESSION['centreName'] . "'";
		$result = mysql_query($qry);
		while ($row = mysql_fetch_array($result)) {
			$centreId = intval($row['CentreId']);
		}
	}

	if ($centreId == 0) {
		$clwhere = "client.CentreId is not null";
		$where = "CentreId is not null";
		$kwhere = "INNER JOIN centre on client.CentreId= centre.CentreId where client.CentreId is not null order by $sort $order";
	} else {
		$clwhere = "client.CentreId=$centreId";
		$where = "CentreId=$centreId";
		$kwhere = "INNER JOIN centre on client.CentreId=centre.CentreId where client.CentreId=$centreId order by $sort $order";
	}
	

	//date range
	$range = (strtotime($endDate)- strtotime($startDate))/24/3600;

	$_SESSION["startDate"] = $_REQUEST['startDate'];
	$_SESSION["endDate"] = $_REQUEST['endDate'];
	$_SESSION["centreId"] = $centreId;
	$_SESSION["activityId"] = $ActivityId;
	if ($userType != "Therapy Assistant") {
		echo"

			<form action='Reports/activityexport.php' method='post'>
			<p><input type='submit'button class='btn' id='btnExport'value='Export'onClick='window.location.href='Reports/activityexport.php'

			></p></form>";
	}

	$actsql = "Select ActivityName from activity where ActivityId='$ActivityId'";
	$resultactsql = mysql_query($actsql);
	while ($row = mysql_fetch_array($resultactsql)) {
		$ActivityName = $row['ActivityName'];
	}
	echo "<div class='tablereport' id='table'style='width:980px;overflow:scroll'>
			<table id = 'tableExcel' cellspacing='2' cellpadding='2' align='center' name='tableatt'   >   
						<tr>
			<td>
			S/No</td>
			<td>
			NRIC</td>
			<td>
			Name</td>";
	if ($userType == "System Admin") {
		echo "
						<td>Centre Name</td>";
	}
	echo "<td>Daycare</td>";
	echo "<td>Age</td>";
	include 'conn.php';
	
	
	//Date columns
	$sqlDBManager = new SqlDBManager();
	
	$sql="select date from holiday";
	$res=$sqlDBManager->queryRows($sql);
	
	$Holiday=array();

	if(!empty($res)){
		for($i=0;$i<count($res);$i++)
		{
			array_push($Holiday, $res[$i]);
		}
	}

	for($i=0;$i<=$range;$i++){
		$date=Date('Y-m-d', strtotime($startDate."+".$i." days"));
		for($j=0;$j<count($Holiday);$j++){
			if($Holiday[$j][0]==Date('Y-m-d', strtotime($startDate."+".$i." days")) || date('l', strtotime(Date('Y-m-d', strtotime($startDate."+".$i." days"))))== "Sunday" || date('l', strtotime(Date('Y-m-d', strtotime($startDate."+".$i." days"))))== "Saturday"){
				$i++;
			}		
		}
		$realDate=Date('d/m/Y', strtotime($startDate."+".$i." days"));
 		echo "<td>" . $realDate . "</td>";	
	}
	echo "<td>Average Performance</td>
			<td>Total Attendance</td></tr>";
	$ttlattendance = 0;
	include 'conn.php';
	$sql = "SELECT * from Client $kwhere";
	$result = mysql_query($sql,$conn);
	$sno = 1;
	$age = 0;
	while ($row = mysql_fetch_array($result)) {
		echo "
			<tr>
			<td>
			" . $sno . "</td>
			<td>
			" . $row['NRIC'] . "</td>
			<td>
			" . $row['CustomerName'] . "</td>";
		if ($userType == "System Admin") {
			echo "
					   <td>
			" . $row['CentreName'] . "</td>";
		}
		echo "<td>
			" . $row['Daycare'] . "</td>";
		$age = date('Y-m-d') - $row['DateOfBirth'];
		echo "<td>" . $age . "</td>";
		$checkDate = $startDate;
		$workingday = 0;
		$attendance = 0;
		$totalPerformance = 0;
		while ($checkDate <= $endDate) {
			$sqlhols1 = "Select count(*) from holiday where Date='$checkDate'";
			$resulthols1 = mysql_query($sqlhols1);
			while ($rowhol = mysql_fetch_array($resulthols1)) {
				if ($rowhol['count(*)'] == 0) {
					$day = date('l', strtotime($checkDate));
					if ($day != 'Sunday' && $day != 'Saturday') {
						$sqlattendance = "Select * from activityrecord where date(Date)='$checkDate' and ActivityId='$ActivityId' and NRIC='" . $row['NRIC'] . "' and $where";
						$checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
						$workingday++;
						$resultattendance = mysql_query($sqlattendance);
						if (mysql_num_rows($resultattendance) == 1) {
							$row1 = mysql_fetch_array($resultattendance);
							$performance = $row1['Performance'];

							echo "<td>$performance</td>";
							$attendance++;
							$totalPerformance += $performance;
						} else {
							echo "<td> - </td>";
						}
					}
					else
						$checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
				} else {
					$checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
				}
			}
		}
		$ttlattendance += $attendance;
		if ($attendance == 0) {
			$averagePerformance = 0;
		} else {
			$averagePerformance = round($totalPerformance / $attendance, 5);
		}
		$avg = round($ttlattendance / $sno, 5);
		echo "<td>
			" . $averagePerformance . "
			</td>
						<td>
			" . $attendance . "
			</td>";
		echo"</tr>";
		$sno++;
	}
	echo "
		</table></div><p>
				</p>
				<h4>Total Attendance for " . $ActivityName . " : " . $ttlattendance . "</h4><br>
				<h4>Average Attendance for " . $ActivityName . " : " . $avg . "</h4>    
				<br>";
?>